"""
Case Type   : 服务端工具
Case Name   : 指定连接的用户名正确和密码正确(如果主机的认证策略是trust，连接用户不是系统管理员)
Create At   : 2024/8/8
Owner       : lvlintao666
Description :
    1.创建数据
    2.导出数据
    3.导入数据
    4.清理环境
Expect      :
    1.创建数据成功
    2.导出数据成功
    3.导入数据成功(表数据导入成功)
    4.清理环境成功
History     :
"""

import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH


class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.constant = Constant()
        self.dbuser_node = Node('dbuser')
        self.commonsh = CommonSH('dbuser')
        self.db_name = 'db_test'

    def test_server_tools1(self):
        self.log.info("--Opengauss_Function_Tools_gs_restore_Case0079开始执行--")
        self.log.info("----------------------创建数据-----------------------")
        sql = f'create database {self.db_name};'
        sql_cmd = self.commonsh.execut_db_sql(sql,
                                              dbname=self.dbuser_node.db_name)
        self.log.info(sql_cmd)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_cmd)
        sql = f'''create table test (id int,name char(20));
            insert into test values(1,'xixi'),(2,'haha'),(3,'hehe');
            create user manager identified by '{macro.PASSWD_REPLACE}';
            grant all privileges to manager;
            create tablespace ds_location1 relative location \
            'test_1/test_2';
            alter tablespace ds_location1 rename to ds_location3;
            create schema schema1;
            create function func_increment_sql(i integer)
            returns integer
            as \$$
            begin
                return i+1;
            end;
            \$$ language plpgsql;
            select * from test;
            '''
        sql_cmd = self.commonsh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(sql_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_cmd)
        self.assertIn('3 rows', sql_cmd)

        self.log.info("----------------导出tar格式文件-----------------")
        dump_cmd = f"source {macro.DB_ENV_PATH};" \
            f" gs_dump -p {self.dbuser_node.db_port} " \
            f"{self.db_name}  " \
            f"-f {macro.DB_INSTANCE_PATH}/test.tar -F t"
        self.log.info(dump_cmd)
        dump_msg = self.dbuser_node.sh(dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(self.constant.GS_DUMP_SUCCESS_MSG, dump_msg)

        self.log.info("--------------导入之前导出的数据----------------")
        restore_cmd = f"source {macro.DB_ENV_PATH};" \
            f"gs_restore  -p {self.dbuser_node.db_port} " \
            f"-d {self.db_name} -U manager " \
            f"-W {macro.PASSWD_REPLACE} {macro.DB_INSTANCE_PATH}/test.tar -c"
        self.log.info(restore_cmd)
        msg = self.dbuser_node.sh(restore_cmd).result()
        self.log.info(msg)
        self.assertIn('restore operation successful', msg)

        self.log.info("-------------查看数据是否导入-------------")
        sql = 'select * from test;'
        sql_cmd = self.commonsh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(sql_cmd)
        self.assertIn('3 rows', sql_cmd)

    def tearDown(self):
        self.log.info("------------------清理环境------------------")
        sql_cmd = self.commonsh.execut_db_sql(f'drop database '
                                              f'{self.db_name};'
                                              f'drop tablespace '
                                              f'ds_location3;'
                                              f'revoke all privileges '
                                              f'from manager;'
                                              f'drop user manager;')
        self.log.info(sql_cmd)
        rm_cmd = f"rm -rf {macro.DB_INSTANCE_PATH}/test.tar"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.log.info("-Opengauss_Function_Tools_gs_restore_Case0079执行结束-")
